package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import entity.ProductCategory;
import utils.PageUtils;

public class ProductCategoryDao extends BaseDao{
   public Connection conn;
   public PreparedStatement pstmt;
   public ResultSet rs;
   
   //查询所以分类
   public List<ProductCategory> prcatList(){
	   List<ProductCategory> list=new ArrayList<ProductCategory>();
	   try {
			conn=super.getConnection();

			String sql="SELECT * from easybuy_product_category";

			pstmt=conn.prepareStatement(sql);

			rs=pstmt.executeQuery();

			while (rs.next()) {
				ProductCategory prcat=new ProductCategory();
				prcat.setId(rs.getInt("id"));
				prcat.setName(rs.getString("name"));
				prcat.setParentId(rs.getInt("parentId"));
				prcat.setType(rs.getInt("type"));
				list.add(prcat);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			super.closeAll(conn, pstmt, rs);
		}
		return list;
   }
	public PageUtils<ProductCategory> findAllProductCategory(int beginIndex, int pageSize) {
		PageUtils<ProductCategory> pageUtil = new PageUtils<>();
		pageUtil.setPageSize(pageSize);
		List<ProductCategory> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "select id,name,parentid,type,iconClass from easybuy_product_category ";
			
			sql += "limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, beginIndex);
			pstmt.setObject(2, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				ProductCategory category = new ProductCategory();
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setParentId(rs.getInt("parentid"));
				category.setType(rs.getInt("type"));
				category.setIconClass(rs.getString("iconClass"));
				list.add(category);
			}
			//将集合数据存到pageUtil里
			pageUtil.setList(list);
			
			int totalCount = 0;
			//查询记录数
			String sqlCount = "select count(1) "
					+ " from easybuy_product_category ";
			
			pstmt = conn.prepareStatement(sqlCount);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			pageUtil.setTotalCount(totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return pageUtil;
	}

	public List<ProductCategory> findAllByParentId(int parentId) {
		List<ProductCategory> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			String sql = "select id,name,parentid,type,iconClass from easybuy_product_category where parentId=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, parentId);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				
				ProductCategory category = new ProductCategory();
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setParentId(rs.getInt("parentid"));
				category.setType(rs.getInt("type"));
				category.setIconClass(rs.getString("iconClass"));
				
				list.add(category);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return list;
	}

	public int add(ProductCategory category) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" INSERT into easybuy_product_category(name,parentId,type,iconClass) values(?,?,?,?) ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, category.getName());
    		pstmt.setObject(2, category.getParentId());
    		pstmt.setObject(3, category.getType());
    		pstmt.setObject(4, category.getIconClass());

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}

	public ProductCategory findById(int id) {
		ProductCategory productCategory = null;
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "SELECT id,name,parentId,type,iconClass  FROM easybuy_product_category where id = ? ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, id);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				productCategory = new ProductCategory();
				productCategory = new ProductCategory();
				productCategory.setId(rs.getInt("id"));
				productCategory.setName(rs.getString("name"));
				productCategory.setParentId(rs.getInt("parentId"));
				productCategory.setType(rs.getInt("type"));
				productCategory.setIconClass(rs.getString("iconClass"));
		        
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		
		return productCategory;
	}

	public List<ProductCategory> findListByType(int type) {
		List<ProductCategory> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			String sql = "select id,name,parentid,type,iconClass from easybuy_product_category where type=? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, type);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				
				ProductCategory category = new ProductCategory();
				category.setId(rs.getInt("id"));
				category.setName(rs.getString("name"));
				category.setParentId(rs.getInt("parentid"));
				category.setType(rs.getInt("type"));
				category.setIconClass(rs.getString("iconClass"));
				
				list.add(category);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return list;
	}

	public int update(ProductCategory category) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql="  UPDATE easybuy_product_category SET name=?,parentId=?,type=?,iconClass=? WHERE id =?   ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, category.getName());
    		pstmt.setObject(2, category.getParentId());
    		pstmt.setObject(3, category.getType());
    		pstmt.setObject(4, category.getIconClass());
    		pstmt.setObject(5, category.getId());

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}
	public int detele(int id) {
		int result2=0;
    	try {
    		if(selectid(id)==0){
    		   conn = super.getConnection();
    		
    		   String sql="  DELETE  from easybuy_product_category  WHERE id =?  ";
    			
    		
    			pstmt = conn.prepareStatement(sql);
        		
        		pstmt.setObject(1, id);
                
        		result2 = pstmt.executeUpdate();
    		}else{
    			System.out.println("不能删除此分类");
    		}
    		
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result2;
	}
	public int selectid(int id) {
		int result2=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" select count(*) from easybuy_product_category where parentId=?";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, id);

    		rs = pstmt.executeQuery();
    		if(rs.next()){
    			result2=rs.getInt("count(*)");
    		}
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result2;
	}

}
